<?php
namespace app\crontab\controller;

class Tools extends Crontab 
{
    public function barea()
    {
        $map = [
            'vehicle_id' => ['NEQ',0]
        ];
        $rows = db('Bill')->where($map)->select();
        foreach ($rows as $v) {
            if( !$vehicle = db('Vehicle')->where(['id'=>$v['vehicle_id']])->find() ) continue;

            $v['area'] = $vehicle['area'];
            $v['area_id'] = $vehicle['area_id'];
            db('Bill')->update($v);
        }
    }
    // 车辆开支、收益导入
    public function oldBill()
    {
        $data = [
            'url' => 'http://baotaiapi.zrjt4000602666.com/bill.xlsx'
        ];

        $result = action('common/PhpOffice/getExcelDetail',$data);

        foreach ($result as $k => $v) {
            $plateNumber = $v[3];
            $map = [
                'plate_number' => trim( $plateNumber )
            ];

            // 找不到对应车辆，就下一条
            $vehicle = db('Vehicle')->where($map)->find();

            # 创建时间
            $createTime = strtotime($v['4'].'-'.$v['5'].'-1 00:00:00');

            # 分类 统筹分期、车队垫付、返点、管理费杂费、转出费、空白、斜杠
            $cate = $v[8];
            $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle);

            /*
            switch ($cate) {
                case '统筹分期':
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'分期');
                    break;
                case '车队垫付':
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'垫付');
                    break;
                case '返点':
                    # 返点就是和上达合作，应收上达的钱
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'返点');
                    break;
                case '管理费、其他杂费':
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'管理费、其他杂费');
                    break;
                case '转出费':
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'转出费');
                    break;
                case '/':
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'斜杠');
                    break;
                
                default:
                    $this->_createDianfuBill($plateNumber,$createTime,$v,$vehicle,'空白');
                    break;
            }
            */
        }
    }

    private function _createDianfuBill($plateNumber,$createTime,$v,$vehicle)
    {
        $amount = $v[6];
        if(!($amount > 0)) return;
        $deadline = $v[8];
        if($deadline == '/' || $deadline == '中润' || $deadline == '中润1' || $deadline == '上达' || $deadline == '上达1') $deadline = '2099-12-31';
        $deadline = date('Y-m-d', strtotime($deadline));
        echo $v[0].' '.$deadline.'<br />';
        $cate = '垫付';
        // if($cate == '/' || $cate == '') $cate = '空白';
        // if($cate == '车队垫付') $cate = '垫付';
        // if($cate == '统筹分期') $cate = '分期';
        # 生成账单主表
        $data = [
            'cate' => $cate,
            'relation_id' => 0,
            'vehicle_id' => $vehicle ? $vehicle['id'] : 0,
            'amount' => $amount,
            'times' => 1,
            'interest' => 0,
            'interest_rate' => 0,
            'deadline' => $deadline ? $deadline : '2099-12-31',
            'create_user' => '旧数据导入',
            'create_user_id' => 0,
            'create_time' => $createTime,
            'old_lixi' => $v[7] ? $v[7] : '',
            'old_huikuan1' => $v[9] ? $v[9] : ''
        ];
        $bId = db('Bill')->insertGetId($data);
        # 生成账单明细

        $data = [
            'bill_id' => $bId,
            'amount' => $amount,
            'interest' => 0,
            'deadline' => $deadline ? $deadline : '2099-12-31',
            'times' => 0, 
        ];
        db('BillDetail')->insert($data);
    }


    // 车辆开支、收益导入
    public function cost()
    {
        $data = [
            'url' => 'http://baotaiapi.zrjt4000602666.com/cost.xlsx'
        ];

        $result = action('common/PhpOffice/getExcelDetail',$data);

        foreach ($result as $k => $v) {
            $plateNumber = $v[3];
            $map = [
                'plate_number' => trim( $plateNumber )
            ];

            // 找不到对应车辆，就下一条
            if( !$vehicle = db('Vehicle')->where($map)->find() ) continue;

            # 创建时间
            $createTime = strtotime($v['6'].'-'.$v['7'].'-1 00:00:00');

            # 收支
            $this->_create_cost($v,$vehicle);

            # 更新车辆利润
            action('common/Vehicle/updateProfit',['vehicleId'=>$vehicle['id']]);
    

        }
    }

    # 生成强险的收支
    private function _create_cost($v,$vehicle)
    {
        # 创建收入
        
        $data['io_type'] = '收入';
        $data['images'] = '';
        $data['vehicle_id'] = $vehicle['id'];
        
        $data['status'] = 20; // 添加、编辑状态默认是0，重新提交也要设置状态为0
        $data['create_user'] = '旧数据导入';
        $data['create_time'] = strtotime($v['6'].'-'.$v['7']);



        # 强险
        $amount = formatMoneySave($v['13']);
        if($amount != 0)
        {
            if($amount > 0)
            {
                $data['cate'] = '交强险返点收入';
                $data['amount'] = $amount;
                db('VehicleIncomeExpense')->insert($data);
            }
            if($amount < 0)
            {
                $data['io_type'] = '支出';
                $data['cate'] = '调账支出';
                $data['amount'] = $amount * -1;
                db('VehicleIncomeExpense')->insert($data);
            }
            
        }
        
        # 商业险
        $amount = formatMoneySave($v['16']) + formatMoneySave($v['17']);
        if($amount != 0)
        {
            if($amount > 0)
            {
                $data['io_type'] = '收入';
                $data['cate'] = '商业险返点收入';
                $data['amount'] = $amount;
                db('VehicleIncomeExpense')->insert($data);
            }
            if($amount < 0 )
            {
                $data['io_type'] = '支出';
                $data['cate'] = '调账支出';
                $data['amount'] = $amount * -1;
                db('VehicleIncomeExpense')->insert($data);
            }   
        }

        # 加价
        $amount = formatMoneySave($v['18']);
        if($amount != 0)
        {
            if($amount > 0)
            {
                $data['io_type'] = '收入';
                $data['cate'] = '商业险加价收入';
                $data['amount'] = $amount;
                db('VehicleIncomeExpense')->insert($data);
            }
            if($amount < 0)
            {
                $data['io_type'] = '支出';
                $data['cate'] = '调账支出';
                $data['amount'] = $amount * -1;
                db('VehicleIncomeExpense')->insert($data);
            }
            
        }
        # 超赔
        $amount = formatMoneySave($v['23']);
        if($amount != 0)
        {
            if($amount > 0)
            {
                $data['io_type'] = '收入';
                $data['cate'] = '超赔险返点收入';
                $data['amount'] = $amount;
                db('VehicleIncomeExpense')->insert($data);
            }
            if($amount < 0)
            {
                $data['io_type'] = '支出';
                $data['cate'] = '调账支出';
                $data['amount'] = $amount * -1;
                db('VehicleIncomeExpense')->insert($data);
            }
            
        }
        # 管理费
        $amount = formatMoneySave($v['25']);
        if($amount != 0)
        {
            if($amount > 0)
            {
                $data['io_type'] = '收入';
                $data['cate'] = '管理费收入';
                $data['amount'] = $amount;
                db('VehicleIncomeExpense')->insert($data);
            }
            if($amount < 0)
            {
                $data['io_type'] = '支出';
                $data['cate'] = '调账支出';
                $data['amount'] = $amount * -1;
                db('VehicleIncomeExpense')->insert($data);
            }

            
        }
        # 二保费
        $amount = formatMoneySave($v['27']);
        if($amount > 0)
        {
            $data['cate'] = '二保费收入';
            $data['amount'] = $amount;
            db('VehicleIncomeExpense')->insert($data);
        }
        if($amount < 0)
        {

            $data['cate'] = '二保费支出';
            $data['amount'] = $amount * -1;
            $ebData = $data;
            $ebData['io_type'] = '支出';
            db('VehicleIncomeExpense')->insert($ebData);
        }
        # gps
        $amount = formatMoneySave($v['29']);
        if($amount > 0)
        {
            $data['cate'] = 'gps费收入';
            $data['amount'] = $amount;
            db('VehicleIncomeExpense')->insert($data);
        }
        if($amount < 0)
        {

            $data['cate'] = 'GPS费支出';
            $data['amount'] = $amount * -1;
            $data['io_type'] = '支出';
            db('VehicleIncomeExpense')->insert($data);
        }
        # 检车费
        $amount = formatMoneySave($v['31']);
        if($amount > 0)
        {
            $data['io_type'] = '收入';
            $data['cate'] = '检车费收入';
            $data['amount'] = $amount;
            db('VehicleIncomeExpense')->insert($data);
        }
        if($amount < 0)
        {

            $data['cate'] = '检车费支出';
            $data['amount'] = $amount * -1;
            $data['io_type'] = '支出';
            db('VehicleIncomeExpense')->insert($data);
        }

        # 学习费
        $amount = formatMoneySave($v['33']);
        if($amount > 0)
        {
            $data['io_type'] = '收入';
            $data['cate'] = '学习费收入';
            $data['amount'] = $amount;
            db('VehicleIncomeExpense')->insert($data);
        }
        if($amount < 0)
        {

            $data['cate'] = '学习费支出';
            $data['amount'] = $amount * -1;
            $data['io_type'] = '支出';
            db('VehicleIncomeExpense')->insert($data);
        }

        # 其他费
        $amount = formatMoneySave($v['39']);
        if($amount > 0)
        {
            $data['io_type'] = '收入';
            $data['cate'] = $v['37'] ? $v['37'] : '无数据';
            $data['amount'] = $amount;
            db('VehicleIncomeExpense')->insert($data);
        }
        if($amount < 0)
        {
            $data['io_type'] = '支出';
            $data['cate'] = $v['37'] ? $v['37'] : '无数据';
            $data['amount'] = $amount * -1;
            db('VehicleIncomeExpense')->insert($data);
        }
        # 优惠
        $amount = formatMoneySave($v['36']);
        if($amount != 0)
        {
            $data['io_type'] = $amount > 0 ? '收入' : '支出';
            if($amount < 0) $amount = $amount * -1;
            $data['cate'] = $v['34'] ? $v['34'] : '无数据';
            $data['amount'] = $amount;
            db('VehicleIncomeExpense')->insert($data);
        }

    }   


    // 转出
    public function out()
    {die;
        $data = [
            'url' => 'http://baotaiapi.zrjt4000602666.com/24out.xlsx'
        ];

        $result = action('common/PhpOffice/getExcelDetail',$data);

        foreach ($result as $k => $v) {
            $plateNumber = $v[4];
            $map = [
                'plate_number' => $plateNumber
            ];

            // 找不到对应车辆，就下一条
            if( !$vehicle = db('Vehicle')->where($map)->find() ) continue;
            $createTime = strtotime( $v[1] );

            $grandfather = $v[5];
            if( $grandfather == '/' || !$grandfather )
            {
                $grandfather = '';
            }
            else
            {
                $grandfather = $grandfather;
            }
            $remark = $v[8] ? $v[8] : '';
            $paid = formatMoneySave( $v[6] );
            $owe = formatMoneySave( $v[7] );
            $amount = bcadd($paid, $owe);

            // 把车辆状态设为已转出
            db('Vehicle')->where($map)->update(['status'=>'转出']);

            // 插入转出记录
            $data = [
                'create_time' => $createTime,
                'create_user' => '批量导入',
                'amount' => $amount,
                'paid' => $paid,
                'grandfather' => $grandfather,
                'remark' => $remark,
                'vehicle_id' => $vehicle['id']
            ];
            $id = db('VehicleOut')->insertGetId($data);


            // 有挂账，录入挂账
            if($owe > 0)
            {
                # 生成账单主表
                $data = [
                    'cate' => '转出',
                    'relation_id' => $id,
                    'vehicle_id' => $vehicle['id'],
                    'amount' => $owe,
                    'grandfather' => $grandfather,
                    'interest' => 0,
                    'interest_rate' => 0,
                    'times' => 1,
                    'deadline' => date('Y-m-d',bcadd($createTime,2592000)),
                    'create_user' => '批量导入',
                    'create_user_id' => 0,
                    'create_time' => $createTime,
                ];
                $bId = db('Bill')->insertGetId($data);
                # 生成账单明细

                $data = [
                    'bill_id' => $bId,
                    'amount_total' => $owe,
                    // 'amount' => $amountOfMonth,
                    // 'interest' => $interestOfMonth,
                    'deadline' => bcadd($createTime,2592000),
                    'times' => 1, // 第几期
                ];
                db('BillDetail')->insert($data);
            }
            

        }
    }
    // 导入保险到期日期，菲菲的表
    // 截止 1244行 2024年08月29日15:47:36
	public function bx()
    {die;
        $data = [
            'url' => 'http://baotaiapi.zrjt4000602666.com/bx.xlsx'
        ];

        $result = action('common/PhpOffice/getExcelDetail',$data);


        foreach ($result as $k => $v) {
            $plateNumber = $v[2];
            $insuranceCompanyC = $v[5];
            $newDateC = strtotime( $v[7] );
            $insuranceCompanyB = $v[10];
            $newDateB = strtotime( $v[7] );
            // 通过车牌查找对应车辆，有就继续
            $map = [
                'plate_number' => $plateNumber
            ];
            if( !$car = db('Vehicle')->where($map)->find() ) continue;
            // 判断菲菲的数据是否比原数据的到期日期新，是就继续
            $oldDateB = strtotime( $car['insurance_date_b'] );
            $oldDateC = strtotime( $car['insurance_date_c'] );
            if($newDateC > $oldDateC)
            {
                $car['insurance_date_c'] = date('Y-m-d',$newDateC);
            }
            if($newDateB > $oldDateB)
            {
                $car['insurance_date_b'] = date('Y-m-d',$newDateB);
            } 

            // 更新车辆保险到期数据
            db('Vehicle')->update($car);

        }

    }
    public function importExcel()
    {
        $data = [
            'url' => 'http://baotaiapi.zrjt4000602666.com/gg.xlsx'
        ];

        $result = action('common/PhpOffice/getExcelDetail',$data);
        $batid = time();
        $errList = [];
        foreach ($result as $k => $v) {

            $row = $k + 1;
            

            $res = $this->_insertVehicle($v);
            if($res !== true)
            {
                $errList[] = '第'.$row.'行数据有误：'.$res;
            }

        }

        foreach ($errList as $v) {
            echo $v.'<br>';
        }
    }
    // 导入车辆，新的，最后2列带有保险到期日
    public function importExcelNew()
    {die;
        $data = [
            'url' => 'http://baotaiapi.zrjt4000602666.com/g.xlsx'
        ];

        $result = action('common/PhpOffice/getExcelDetail',$data);
        $batid = time();
        $errList = [];
        foreach ($result as $k => $v) {

            $row = $k + 1;
            

            $res = $this->_insertVehicle($v);
            if($res !== true)
            {
                $errList[] = '第'.$row.'行数据有误：'.$res;
            }

        }

        foreach ($errList as $v) {
            echo $v.'<br>';
        }
    }
    private function _getCompany($name)
    {
        $map = [
            'title' => $name
        ];
        if(!$com = db('Company')->where($map)->find())
        {
            $data = [
            	'title' => $name ? $name : '',
            	'area_id' => 4,
            	'area' => 'D'
            ];
            $id = db('Company')->insertGetId($data);
            $data['id'] = $id;
            return $data;
        }
        return $com;
    }

    // 只验证车牌号
    private function _verifyRepeat2($plate_number)
    {
        $map = [
            'plate_number' => $plate_number
        ];
        if(db('Vehicle')->where($map)->find()) return '车牌号'.$plate_number.'的车辆已存在系统中，无法重复添加';
        // $map = [
        //     'vin_number' => $vin_number
        // ];
        // if(db('Vehicle')->where($map)->find()) return '车架号'.$vin_number.'的车辆已存在系统中，无法重复添加';

        return true;
    }
    private function _verifyRepeat($vin_number,$plate_number)
    {
        $map = [
            'plate_number' => $plate_number
        ];
        if(db('Vehicle')->where($map)->find()) return '车牌号'.$plate_number.'的车辆已存在系统中，无法重复添加';
        $map = [
            'vin_number' => $vin_number
        ];
        if(db('Vehicle')->where($map)->find()) return '车架号'.$vin_number.'的车辆已存在系统中，无法重复添加';

        return true;
    }

    private function _insertVehicle($v)
    {
    	// 检查重复
        $repeat = $this->_verifyRepeat2($v[4]);
        if($repeat !== true) return $repeat;
        $company = $this->_getCompany($v[3]);
        if(!is_array($company)) return $company;

        // 去null
        $count = count($v);
        for ($i=0; $i < $count; $i++) { 
            if($v[$i] == null) $v[$i] == '';
        }

        $data = [
            'status' => '正常',
            // 'vin_number' => $v['14'] == null ? '' : $v['14'],
            'plate_number' => $v['4'] == null ? '' : $v['4'],
            'company_id' => $company['id'],
            'company' => $company['title'],
            'area' => $company['area'],
            'area_id' => $company['area_id'],
            'm_id' => $v['1'] == null ? '' : $v['1'],
            'realname' => $v['5'] == null ? '' : $v['5'],
            'idcard' => $v['6'] == null ? '' : $v['6'],
            'tel' => $v['7'] == null ? '' : $v['7'],
            'address' => $v['8'] == null ? '' : $v['8'],
            'brand' => $v['9'] == null ? '' : $v['9'], // 品牌
            'vehicle_type' => $v['10'] == null ? '' : $v['10'], // 车辆类型
            'vehicle_model' => $v['11'] == null ? '' : $v['11'], // 车辆型号
            'drive_type' => $v['12'] == null ? '' : $v['12'], // 驱动形式
            'color' => $v['13'] == null ? '' : $v['13'], // 车身颜色
            'vin_number' => $v['14'] == null ? '' : $v['14'], // 车架号
            'engine_number' => $v['15'] == null ? '' : $v['15'], // 发动机号
            'business_cert' => $v['16'] == null ? '' : $v['16'], // 营运证号
            'reg_date' => $v['17'] == null ? '0000-00-00' : date('Y-m-d',strtotime($v['17'])), // 登记日期
            'check_date' => $v['18'] == null ? '0000-00-00' : date('Y-m-d',strtotime($v['18'])), // 检验有效期
            'business_date' => $v['19'] == null ? '0000-00-00' : date('Y-m-d',strtotime($v['19'])), // 营运有效期
            'insurance_date_c' => $v['20'] == null ? '0000-00-00' : date('Y-m-d',strtotime($v['20'])), // 交强有效期
            'insurance_date_b' => $v['21'] == null ? '0000-00-00' : date('Y-m-d',strtotime($v['21'])), // 商业有效期
            'weight' => $v['22'] == null ? '' : $v['22'], // 总质量
            'full_weight' => $v['23'] == null ? '' : $v['23'], // 整备质量
            'approved_weight' => $v['24'] == null ? '' : $v['24'], // 核定质量
            'passenger' => $v['25'] == null ? '' : $v['25'], // 核定载客
            'engine_model' => $v['26'] == null ? '' : $v['26'], // 发动机型号
            'fuel_type' => $v['27'] == null ? '' : $v['27'], // 燃料种类
            'engine_power' => $v['28'] == null ? '' : $v['28'], // 排量功率
            'manufacturer' => $v['29'] == null ? '' : $v['29'], // 制造厂家
            'wheel_track' => $v['30'] == null ? '' : $v['30'], // 轮距
            'wheel_number' => $v['31'] == null ? '' : $v['31'], // 轮胎数
            'wheel_size' => $v['32'] == null ? '' : $v['32'], // 轮胎规格
            'wheel_base' => $v['33'] == null ? '' : $v['33'], // 轴距
            'outline_size' => $v['34'] == null ? '' : $v['34'], // 外廓尺寸
            'cargo_size' => $v['35'] == null ? '' : $v['35'], // 货箱尺寸
            'status' => $v['36'] == null ? '正常' : $v['36'], // 状态
            'contract_start' => $v['37'] == null ? '0000-00-00' : $v['37'], // 签合同日期
            'contract_end' => $v['38'] == null ? '0000-00-00' : $v['38'], // 合同到期
            'invoice_value' => $v['39'] == null ? '' : $v['39'], // 发票金额
            // 'manage_fee' => $v['40'] == null ? '' : $v['5'], // gps平台
            // 'manage_fee' => $v['41'] == null ? '' : $v['41'], // 管理费
            // 'remark' => $v['42'] == null ? '' : $v['42'], // 备注
            // 'id_card' => $v['43'], // 数量
            // 'vehicle_report_type' => $v['44'] == null ? '' : $v['44'], // 车辆统计类型
            // 'id_card' => $v['45'], // 统计车辆数
            // 'insurance_end_month' => $v['46'] == null ? '' : $v['46'], // 保险到期月份
            // 'id_card' => $v['47'] // 保险月份统计
       
        ];

        db('Vehicle')->insert($data);

        return true;
    }

    
	
}